package com.cinc.ebc.quote.product.controller;

import java.io.InputStream;
import java.io.UnsupportedEncodingException;
import java.io.IOException;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.net.URLDecoder;
import java.util.*;
import java.util.stream.Collectors;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.cinc.ebc.quote.product.model.QuoteProductModel;
import com.cinc.ebc.quote.product.model.QuoteProjectCustomerModel;
import com.cinc.ebc.quote.product.vo.QuoteProductRequestPage;
import com.cinc.ebc.quote.productClass.entity.QuoteProductClass;
import com.cinc.ebc.quote.project.entity.QuoteProject;
import com.cinc.mdm.modules.customer.entity.MdmCustomer;
import com.cinc.mdm.modules.material.entity.MdmMaterial;
import net.sf.saxon.expr.instruct.ValueOf;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.jeecg.common.util.DateUtils;
import org.jeecg.common.util.UUIDGenerator;
import org.jeecgframework.poi.excel.ExcelImportUtil;
import org.jeecgframework.poi.excel.def.NormalExcelConstants;
import org.jeecgframework.poi.excel.entity.ExportParams;
import org.jeecgframework.poi.excel.entity.ImportParams;
import org.jeecgframework.poi.excel.view.JeecgEntityExcelView;
import org.jeecg.common.system.vo.LoginUser;
import org.apache.shiro.SecurityUtils;
import org.jeecg.common.api.vo.Result;
import org.jeecg.common.system.query.QueryGenerator;
import org.jeecg.common.util.oConvertUtils;
import com.cinc.ebc.quote.product.entity.QuoteProductDetail;
import com.cinc.ebc.quote.product.entity.QuoteProductFee;
import com.cinc.ebc.quote.product.entity.QuoteProduct;
import com.cinc.ebc.quote.product.vo.QuoteProductPage;
import com.cinc.ebc.quote.product.service.IQuoteProductService;
import com.cinc.ebc.quote.product.service.IQuoteProductDetailService;
import com.cinc.ebc.quote.product.service.IQuoteProductFeeService;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.servlet.ModelAndView;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.multipart.MultipartHttpServletRequest;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import lombok.extern.slf4j.Slf4j;
import com.alibaba.fastjson.JSON;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import org.jeecg.common.aspect.annotation.AutoLog;

/**
 * @Description: 报价产品
 * @Author: jeecg-boot
 * @Date: 2023-03-22
 * @Version: V1.0
 */
@Api(tags = "报价产品")
@RestController
@RequestMapping("/quote/product")
@Slf4j
public class QuoteProductController {
    @Autowired
    private IQuoteProductService quoteProductService;
    @Autowired
    private IQuoteProductDetailService quoteProductDetailService;
    @Autowired
    private IQuoteProductFeeService quoteProductFeeService;

    /**
     * 分页列表查询
     *
     * @param quoteProduct
     * @param pageNo
     * @param pageSize
     * @param req
     * @return
     */
    @AutoLog(value = "报价产品-分页列表查询")
    @ApiOperation(value = "报价产品-分页列表查询", notes = "报价产品-分页列表查询")
    @GetMapping(value = "/list")
    public Result<?> queryPageList(QuoteProduct quoteProduct,
                                   @RequestParam(name = "pageNo", defaultValue = "1") Integer pageNo,
                                   @RequestParam(name = "pageSize", defaultValue = "10") Integer pageSize,
                                   HttpServletRequest req) {
        QueryWrapper<QuoteProduct> queryWrapper = QueryGenerator.initQueryWrapper(quoteProduct, req.getParameterMap());
        Page<QuoteProduct> page = new Page<QuoteProduct>(pageNo, pageSize);
        IPage<QuoteProduct> pageList = quoteProductService.page(page, queryWrapper);
        return Result.OK(pageList);
    }

    /**
     * 分页列表查询
     *
     * @param quoteProductRequest
     * @param pageNo
     * @param pageSize
     * @param req
     * @return
     */
    @AutoLog(value = "报价产品-分页列表查询")
    @ApiOperation(value = "报价产品-分页列表查询", notes = "报价产品-分页列表查询")
    @GetMapping(value = "/productList")
    public Result<?> queryProductPageList(QuoteProductRequestPage quoteProductRequest,
                                          @RequestParam(name = "pageNo", defaultValue = "1") Integer pageNo,
                                          @RequestParam(name = "pageSize", defaultValue = "10") Integer pageSize,
                                          HttpServletRequest req) {
        Page<QuoteProductModel> page = new Page<>(pageNo, pageSize);
        IPage<QuoteProductModel> pageList = quoteProductService.productPage(page, quoteProductRequest);
        return Result.OK(pageList);
    }

    /**
     * 添加
     *
     * @param quoteProductPage
     * @return
     */
    @AutoLog(value = "报价产品-添加")
    @ApiOperation(value = "报价产品-添加", notes = "报价产品-添加")
    @PostMapping(value = "/add")
    public Result<?> add(@RequestBody QuoteProductPage quoteProductPage) {
        QuoteProduct quoteProduct = new QuoteProduct();
        BeanUtils.copyProperties(quoteProductPage, quoteProduct);
        quoteProductService.saveMain(quoteProduct, quoteProductPage.getQuoteProductDetailList(), quoteProductPage.getQuoteProductFeeList());
        return Result.OK("添加成功！");
    }

    /**
     * 编辑
     *
     * @param quoteProductPage
     * @return
     */
    @AutoLog(value = "报价产品-编辑")
    @ApiOperation(value = "报价产品-编辑", notes = "报价产品-编辑")
    @PutMapping(value = "/edit")
    public Result<?> edit(@RequestBody QuoteProductPage quoteProductPage) {
        QuoteProduct quoteProduct = new QuoteProduct();
        BeanUtils.copyProperties(quoteProductPage, quoteProduct);
        QuoteProduct quoteProductEntity = quoteProductService.getById(quoteProduct.getId());
        if (quoteProductEntity == null) {
            return Result.error("未找到对应数据");
        }
        quoteProductService.updateMain(quoteProduct, quoteProductPage.getQuoteProductDetailList(), quoteProductPage.getQuoteProductFeeList());
        return Result.OK("编辑成功!");
    }

    /**
     * 通过id删除
     *
     * @param id
     * @return
     */
    @AutoLog(value = "报价产品-通过id删除")
    @ApiOperation(value = "报价产品-通过id删除", notes = "报价产品-通过id删除")
    @DeleteMapping(value = "/delete")
    public Result<?> delete(@RequestParam(name = "id", required = true) String id) {
        quoteProductService.delMain(id);
        return Result.OK("删除成功!");
    }

    /**
     * 批量删除
     *
     * @param ids
     * @return
     */
    @AutoLog(value = "报价产品-批量删除")
    @ApiOperation(value = "报价产品-批量删除", notes = "报价产品-批量删除")
    @DeleteMapping(value = "/deleteBatch")
    public Result<?> deleteBatch(@RequestParam(name = "ids", required = true) String ids) {
        this.quoteProductService.delBatchMain(Arrays.asList(ids.split(",")));
        return Result.OK("批量删除成功！");
    }

    /**
     * 通过id查询
     *
     * @param id
     * @return
     */
    @AutoLog(value = "报价产品-通过id查询")
    @ApiOperation(value = "报价产品-通过id查询", notes = "报价产品-通过id查询")
    @GetMapping(value = "/queryById")
    public Result<?> queryById(@RequestParam(name = "id", required = true) String id) {
        QuoteProduct quoteProduct = quoteProductService.getById(id);
        if (quoteProduct == null) {
            return Result.error("未找到对应数据");
        }
        return Result.OK(quoteProduct);

    }

    /**
     * 通过id查询
     *
     * @param id
     * @return
     */
    @AutoLog(value = "报价产品明细通过主表ID查询")
    @ApiOperation(value = "报价产品明细主表ID查询", notes = "报价产品明细-通主表ID查询")
    @GetMapping(value = "/queryQuoteProductDetailByMainId")
    public Result<?> queryQuoteProductDetailListByMainId(@RequestParam(name = "id", required = true) String id) {
        List<QuoteProductDetail> quoteProductDetailList = quoteProductDetailService.selectByMainId(id);
        return Result.OK(quoteProductDetailList);
    }

    /**
     * 通过id查询
     *
     * @param id
     * @return
     */
    @AutoLog(value = "报价产品费用通过主表ID查询")
    @ApiOperation(value = "报价产品费用主表ID查询", notes = "报价产品费用-通主表ID查询")
    @GetMapping(value = "/queryQuoteProductFeeByMainId")
    public Result<?> queryQuoteProductFeeListByMainId(@RequestParam(name = "id", required = true) String id) {
        List<QuoteProductFee> quoteProductFeeList = quoteProductFeeService.selectByMainId(id);
        return Result.OK(quoteProductFeeList);
    }

    /**
     * 导出excel
     *
     * @param request
     * @param quoteProduct
     */
    @RequestMapping(value = "/exportXls")
    public ModelAndView exportXls(HttpServletRequest request, QuoteProduct quoteProduct) {
        // Step.1 组装查询条件查询数据
        QueryWrapper<QuoteProduct> queryWrapper = QueryGenerator.initQueryWrapper(quoteProduct, request.getParameterMap());
        LoginUser sysUser = (LoginUser) SecurityUtils.getSubject().getPrincipal();

        //Step.2 获取导出数据
        List<QuoteProduct> queryList = quoteProductService.list(queryWrapper);
        // 过滤选中数据
        String selections = request.getParameter("selections");
        List<QuoteProduct> quoteProductList = new ArrayList<QuoteProduct>();
        if (oConvertUtils.isEmpty(selections)) {
            quoteProductList = queryList;
        } else {
            List<String> selectionList = Arrays.asList(selections.split(","));
            quoteProductList = queryList.stream().filter(item -> selectionList.contains(item.getId())).collect(Collectors.toList());
        }

        // Step.3 组装pageList
        List<QuoteProductPage> pageList = new ArrayList<QuoteProductPage>();
        for (QuoteProduct main : quoteProductList) {
            QuoteProductPage vo = new QuoteProductPage();
            BeanUtils.copyProperties(main, vo);
            List<QuoteProductDetail> quoteProductDetailList = quoteProductDetailService.selectByMainId(main.getId());
            vo.setQuoteProductDetailList(quoteProductDetailList);
            List<QuoteProductFee> quoteProductFeeList = quoteProductFeeService.selectByMainId(main.getId());
            vo.setQuoteProductFeeList(quoteProductFeeList);
            pageList.add(vo);
        }

        // Step.4 AutoPoi 导出Excel
        ModelAndView mv = new ModelAndView(new JeecgEntityExcelView());
        mv.addObject(NormalExcelConstants.FILE_NAME, "报价产品列表");
        mv.addObject(NormalExcelConstants.CLASS, QuoteProductPage.class);
        mv.addObject(NormalExcelConstants.PARAMS, new ExportParams("报价产品数据", "导出人:" + sysUser.getRealname(), "报价产品"));
        mv.addObject(NormalExcelConstants.DATA_LIST, pageList);
        return mv;
    }

    /**
     * 通过excel导入数据
     *
     * @param request
     * @param response
     * @return
     */
    @RequestMapping(value = "/importExcel", method = RequestMethod.POST)
    public Result<?> importExcel(HttpServletRequest request, HttpServletResponse response) {
        MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;
        Map<String, MultipartFile> fileMap = multipartRequest.getFileMap();
        for (Map.Entry<String, MultipartFile> entity : fileMap.entrySet()) {
            MultipartFile file = entity.getValue();// 获取上传文件对象
            ImportParams params = new ImportParams();
            params.setTitleRows(2);
            params.setHeadRows(1);
            params.setNeedSave(true);
            try {
                List<QuoteProductPage> list = ExcelImportUtil.importExcel(file.getInputStream(), QuoteProductPage.class, params);
                for (QuoteProductPage page : list) {
                    QuoteProduct po = new QuoteProduct();
                    BeanUtils.copyProperties(page, po);
                    quoteProductService.saveMain(po, page.getQuoteProductDetailList(), page.getQuoteProductFeeList());
                }
                return Result.OK("文件导入成功！数据行数:" + list.size());
            } catch (Exception e) {
                log.error(e.getMessage(), e);
                return Result.error("文件导入失败:" + e.getMessage());
            } finally {
                try {
                    file.getInputStream().close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
        return Result.OK("文件导入失败！");
    }

    /**
     * 通过项目清单excel导入数据 --- 个性化导入
     *
     * @param request
     * @param response
     * @return
     */
    @RequestMapping(value = "/importQuoteExcel01", method = RequestMethod.POST)
    public Result<?> importQuoteExcel01(HttpServletRequest request, HttpServletResponse response) {
        MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;
        Map<String, MultipartFile> fileMap = multipartRequest.getFileMap();
        for (Map.Entry<String, MultipartFile> entity : fileMap.entrySet()) {
            MultipartFile file = entity.getValue();// 获取上传文件对象
            try {
                Workbook workbook = WorkbookFactory.create(file.getInputStream());
                XSSFFormulaEvaluator evaluator = new XSSFFormulaEvaluator((XSSFWorkbook) workbook);
                // 1.获取项目、客户、产品
                Sheet sheet = workbook.getSheetAt(0);
                int totalRows = sheet.getPhysicalNumberOfRows();
                // 项目
                QuoteProject quoteProject = new QuoteProject();
                quoteProject.setId(UUIDGenerator.generate());
                // 客户
                QuoteProjectCustomerModel quoteCustomer = new QuoteProjectCustomerModel();
                quoteCustomer.setCustomerId(UUIDGenerator.generate());
                // 产品
                List<QuoteProduct> productList = new ArrayList<>();
                List<MdmMaterial> productMaterialList = new ArrayList<>();
                // 解析
                for (int i = sheet.getFirstRowNum(); i < totalRows; i++) {
                    if (sheet.getRow(i) == null) {
                        continue;
                    }
                    for (int j = 0; j < 10; j++) {
                        String cellValue = getCellValueString(sheet.getRow(i).getCell(j), evaluator);
                        if (oConvertUtils.isEmpty(cellValue)) {
                            continue;
                        }
                        int splitIndex = cellValue.indexOf(":") == -1 ? cellValue.indexOf("：") : cellValue.indexOf(":");
                        String realValue = cellValue.substring(splitIndex + 1);
                        if (cellValue.contains("项目名称")) {
                            quoteProject.setProjectName(realValue);
                            continue;
                        }
                        if (cellValue.contains("NO")) {
                            quoteProject.setQuoteNo(realValue);
                            continue;
                        }
                        if (cellValue.contains("TO")) {
                            // 客户
                            quoteCustomer.setCustFullName(realValue);
                            for (int k = j + 1; k < 10; k++) {
                                String subValue = getCellValueString(sheet.getRow(i).getCell(k), evaluator);
                                if (oConvertUtils.isEmpty(subValue)) {
                                    continue;
                                }
                                int subIndex = subValue.indexOf(":") == -1 ? subValue.indexOf("：") : subValue.indexOf(":");
                                if (subIndex < 0) {
                                    continue;
                                }
                                String realSubValue = subValue.substring(subIndex + 1);
                                if (subValue.contains("TEL")) {
                                    quoteCustomer.setInvTel(realSubValue);
                                    continue;
                                }
                                if (subValue.contains("FAX")) {
                                    quoteCustomer.setFax(realSubValue);
                                }
                            }
                            break;
                        }
                        if (cellValue.contains("LINK")) {
                            // 联系人
                            quoteCustomer.setContactsName(realValue);
                            for (int k = j + 1; k < 10; k++) {
                                String subValue = getCellValueString(sheet.getRow(i).getCell(k), evaluator);
                                if (oConvertUtils.isEmpty(subValue)) {
                                    continue;
                                }
                                int subIndex = subValue.indexOf(":") == -1 ? subValue.indexOf("：") : subValue.indexOf(":");
                                if (subIndex < 0) {
                                    continue;
                                }
                                String realSubValue = subValue.substring(subIndex + 1);
                                if (subValue.contains("TEL")) {
                                    quoteCustomer.setContactsPhone(realSubValue);
                                    continue;
                                }
                                if (subValue.contains("DATE")) {
                                    try {
                                        quoteProject.setProjectDate(DateUtils.parseDateAutoCheck(realSubValue));
                                    } catch (Exception e) {
                                        log.error("项目创建日期无法识别：{}", realSubValue);
                                    }
                                }
                            }
                            break;
                        }
                        if (cellValue.contains("序号")) {
                            // 产品列表
                            for (int k = i + 1; k < totalRows; k++) {
                                i = k;
                                if (sheet.getRow(k) == null) {
                                    break;
                                }
                                String cell1Value = getCellValueString(sheet.getRow(k).getCell(1), evaluator);
                                BigDecimal cell6Value = getCellValueNumeric(sheet.getRow(k).getCell(6), evaluator);
                                if(cell1Value.contains("合计")){
                                    quoteProject.setProductPrice(cell6Value);
                                    quoteProject.setAmount(cell6Value);
                                    break;
                                }
                                String cell2Value = getCellValueString(sheet.getRow(k).getCell(2), evaluator);
                                String cell3Value = getCellValueString(sheet.getRow(k).getCell(3), evaluator);
                                BigDecimal cell4Value = getCellValueNumeric(sheet.getRow(k).getCell(4), evaluator);
                                BigDecimal cell5Value = getCellValueNumeric(sheet.getRow(k).getCell(5), evaluator);
                                String cell7Value = getCellValueString(sheet.getRow(k).getCell(7), evaluator);

                                MdmMaterial productMaterial = productMaterialList.stream().filter(material -> {
                                    return cell1Value.equals(material.getMaterialName())
                                            && cell2Value.equals(material.getMaterialSpec());
                                }).findFirst().orElse(null);
                                if (productMaterial == null) {
                                    productMaterial = new MdmMaterial();
                                    productMaterial.setId(UUIDGenerator.generate());
//                                        productMaterial.setMaterialCode(cell1Value);
                                    productMaterial.setMaterialName(cell1Value);
                                    productMaterial.setMaterialSpec(cell2Value);
                                    productMaterial.setMaterialUnitCode(cell3Value);
                                    productMaterialList.add(productMaterial);
                                }
                                //产品
                                QuoteProduct product = new QuoteProduct();
                                product.setId(UUIDGenerator.generate());
                                product.setProjectId(quoteProject.getId());
                                product.setMaterialId(productMaterial.getId());
                                product.setMaterialDesc(productMaterial.getMaterialName() + productMaterial.getMaterialSpec());
                                product.setQty(cell4Value);
                                product.setUnitPrice(cell5Value);
                                product.setSumPrice(cell6Value);
                                product.setRemark(cell7Value);
                                productList.add(product);
                            }
                            break;
                        }
                        if(cellValue.contains("报价说明")){
                            String remark = cellValue;
                            for (int k = i + 1; k < totalRows; k++) {
                                i = k;
                                if (sheet.getRow(k) == null) {
                                    break;
                                }
                                String cell0Value = getCellValueString(sheet.getRow(k).getCell(0), evaluator);
                                if(oConvertUtils.isEmpty(cell0Value)){
                                    continue;
                                }
                                if(cell0Value.contains("制表")){
                                    i = k-1;
                                    break;
                                }
                                remark = remark + cell0Value;
                            }
                            quoteProject.setRemark(remark);
                            break;
                        }
                        if(cellValue.contains("制表")){
                            quoteProject.setQuoteMan(realValue);
                            for (int k = j + 1; k < 10; k++) {
                                String subValue = getCellValueString(sheet.getRow(i).getCell(k), evaluator);
                                if (oConvertUtils.isEmpty(subValue)) {
                                    continue;
                                }
                                int subIndex = subValue.indexOf(":") == -1 ? subValue.indexOf("：") : subValue.indexOf(":");
                                if (subIndex < 0) {
                                    continue;
                                }
                                String realSubValue = subValue.substring(subIndex + 1);
                                if (subValue.contains("审核")) {
                                    quoteProject.setQuoteConfirmMan(realSubValue);
                                    break;
                                }
                            }
                            i = totalRows;
                            break;
                        }
                    }
                }
                // 结果校验
                if (oConvertUtils.isEmpty(quoteProject.getProjectName())) {
                    throw new RuntimeException("未找到项目信息");
                }
                if (oConvertUtils.isEmpty(quoteCustomer.getCustFullName())) {
                    throw new RuntimeException("未找到客户信息");
                }
                if (oConvertUtils.listIsEmpty(productList)) {
                    throw new RuntimeException("未找到产品信息");
                }

                // 2.获取产品明细、产品费用
                sheet = workbook.getSheetAt(1);
                totalRows = sheet.getPhysicalNumberOfRows();
                if (sheet == null) {
                    break;
                }
                List<QuoteProductDetail> productDetailList = new ArrayList<>();
                List<MdmMaterial> productDetailMaterialList = new ArrayList<>();
                List<QuoteProductFee> productFeeList = new ArrayList<>();
                for (int i = 0; i < totalRows; i++) {
                    if (sheet.getRow(i) == null) {
                        continue;
                    }
                    // 查找明细对应产品
                    String product1Value = getCellValueString(sheet.getRow(i).getCell(1), evaluator);
                    String product2Value = getCellValueString(sheet.getRow(i).getCell(2), evaluator);
                    MdmMaterial findProductMaterial = productMaterialList.stream()
                            .filter(material -> product1Value.equals(material.getMaterialName()) && product2Value.equals(material.getMaterialSpec()))
                            .findFirst().orElse(null);
                    if (findProductMaterial == null) {
                        continue;
                    }
                    QuoteProduct findProduct = productList.stream()
                            .filter(product -> product.getMaterialId().equals(findProductMaterial.getId())).findFirst().orElse(null);
                    if (findProduct == null) {
                        continue;
                    }
                    // 解析产品明细
                    for (int j = i + 2; j < totalRows; j++) {
                        i = j;
                        if (sheet.getRow(j) == null) {
                            continue;
                        }
                        String cell1Value = getCellValueString(sheet.getRow(j).getCell(1), evaluator);
                        if (oConvertUtils.isEmpty(cell1Value)) {
                            continue;
                        }
                        if (oConvertUtils.replaceBlank(cell1Value).contains("合计")) {
                            break;
                        }
                        if (cell1Value.contains("费")) {
                            QuoteProductFee quoteProductFee = new QuoteProductFee();
                            quoteProductFee.setId(UUIDGenerator.generate());
                            quoteProductFee.setMainId(findProduct.getId());
                            quoteProductFee.setFeeId(cell1Value);
                            quoteProductFee.setFeePrice(getCellValueNumeric(sheet.getRow(j).getCell(6), evaluator));
                            productFeeList.add(quoteProductFee);
                        } else {
                            String cell2Value = getCellValueString(sheet.getRow(j).getCell(2), evaluator);
                            String cell3Value = getCellValueString(sheet.getRow(j).getCell(3), evaluator);
                            BigDecimal cell4Value = getCellValueNumeric(sheet.getRow(j).getCell(4), evaluator);
                            BigDecimal cell5Value = getCellValueNumeric(sheet.getRow(j).getCell(5), evaluator);
                            BigDecimal cell6Value = getCellValueNumeric(sheet.getRow(j).getCell(6), evaluator);
                            String cell7Value = getCellValueString(sheet.getRow(j).getCell(7), evaluator);
                            String cell8Value = getCellValueString(sheet.getRow(j).getCell(8), evaluator);

                            if (cell4Value.compareTo(BigDecimal.ZERO) == 0) {
                                // 数量为0，无效产品明细
                                continue;
                            }
                            MdmMaterial findProductDetailMaterial = productDetailMaterialList.stream().filter(materialDetail -> {
                                return materialDetail.getMaterialName().equals(cell1Value)
                                        && materialDetail.getMaterialSpec().equals(cell2Value)
                                        && materialDetail.getMaterialFactory().equals(cell7Value);
                            }).findFirst().orElse(null);
                            if (findProductDetailMaterial == null) {
                                // 添加产品明细物料
                                findProductDetailMaterial = new MdmMaterial();
                                findProductDetailMaterial.setId(UUIDGenerator.generate());
                                findProductDetailMaterial.setMaterialName(cell1Value);
                                findProductDetailMaterial.setMaterialSpec(cell2Value);
                                findProductDetailMaterial.setMaterialFactory(cell7Value);
                                findProductDetailMaterial.setMaterialUnitCode(cell3Value);
                                productDetailMaterialList.add(findProductDetailMaterial);
                            }
                            // 添加产品明细
                            QuoteProductDetail quoteProductDetail = new QuoteProductDetail();
                            quoteProductDetail.setId(UUIDGenerator.generate());
                            quoteProductDetail.setMainId(findProduct.getId());
                            quoteProductDetail.setMaterialId(findProductDetailMaterial.getId());
                            quoteProductDetail.setMaterialFactory(findProductDetailMaterial.getMaterialFactory());
                            quoteProductDetail.setMaterialDesc(findProductDetailMaterial.getMaterialName() + findProductDetailMaterial.getMaterialSpec());
                            quoteProductDetail.setUnit(findProductDetailMaterial.getMaterialUnitCode());
                            quoteProductDetail.setSubQty(cell4Value);
                            quoteProductDetail.setQty(cell4Value.multiply(findProduct.getQty()).setScale(2, RoundingMode.HALF_UP));
                            quoteProductDetail.setUnitPrice(cell5Value);
                            quoteProductDetail.setSumPrice(quoteProductDetail.getUnitPrice().multiply(quoteProductDetail.getQty()).setScale(2, RoundingMode.HALF_UP));
                            quoteProductDetail.setSubSumPrice(cell6Value);
                            quoteProductDetail.setRemark(cell8Value);
                            productDetailList.add(quoteProductDetail);
                        }
                    }
                }

                // 3.保存数据
                quoteProductService.saveQuoteProductImport(quoteProject, quoteCustomer, null, productList,
                        productDetailList, productMaterialList, productDetailMaterialList, productFeeList);

                return Result.OK("文件导入成功！");
            } catch (Exception e) {
                log.error(e.getMessage(), e);
                return Result.error("文件导入失败:" + e.getMessage());
            } finally {
                try {
                    file.getInputStream().close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
        return Result.OK("文件导入失败！");
    }

    /**
     * 通过项目清单excel导入数据 --- 标准导入
     *
     * @param request
     * @param response
     * @return
     */
    @RequestMapping(value = "/importQuoteExcel", method = RequestMethod.POST)
    public Result<?> importQuoteExcel(HttpServletRequest request, HttpServletResponse response) {
        MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;
        Map<String, MultipartFile> fileMap = multipartRequest.getFileMap();
        for (Map.Entry<String, MultipartFile> entity : fileMap.entrySet()) {
            MultipartFile file = entity.getValue();// 获取上传文件对象
            try {
                Workbook workbook = WorkbookFactory.create(file.getInputStream());
                XSSFFormulaEvaluator evaluator = new XSSFFormulaEvaluator((XSSFWorkbook) workbook);
                // 1.获取项目、客户、产品分类
                Sheet sheet = workbook.getSheetAt(0);
                int totalRows = sheet.getPhysicalNumberOfRows();
                // 客户
                QuoteProjectCustomerModel quoteCustomer = new QuoteProjectCustomerModel();
                quoteCustomer.setCustomerId(UUIDGenerator.generate());
                // 项目
                QuoteProject quoteProject = new QuoteProject();
                quoteProject.setId(UUIDGenerator.generate());
                quoteProject.setCustomerId(quoteCustomer.getCustomerId());
                // 产品分类
                List<QuoteProductClass> productClassList = new ArrayList<>();
                Map<String, String> productClassNameAndId = new HashMap<>();
                // 解析
                for (int i = sheet.getFirstRowNum(); i < totalRows; i++) {
                    if(sheet.getRow(i) == null){
                        continue;
                    }
                    String cell0Value = getCellValueString(sheet.getRow(i).getCell(0), evaluator);
                    if (cell0Value.equals("工程信息")) {
                        for (int y = i + 1; y < totalRows; y++) {
                            if(sheet.getRow(y) == null){
                                continue;
                            }
                            String projectValue = getCellValueString(sheet.getRow(y).getCell(1), evaluator);
                            String titleValue = getCellValueString(sheet.getRow(y).getCell(0), evaluator);
                            if (titleValue.equals("项目名称")) {
                                quoteProject.setProjectName(projectValue);
                            } else if (titleValue.equals("描述")) {
                                quoteProject.setProjectDesc(projectValue);
                            } else if (titleValue.equals("报价单号")) {
                                quoteProject.setQuoteNo(projectValue);
                            } else if (titleValue.equals("报价人")) {
                                quoteProject.setQuoteMan(projectValue);
                            } else if (titleValue.equals("报价审核人")) {
                                quoteProject.setQuoteConfirmMan(projectValue);
                            } else if (titleValue.equals("项目负责人")) {
                                quoteProject.setOwner(projectValue);
                            } else if (titleValue.equals("创建日期")) {
                                try {
                                    quoteProject.setProjectDate(DateUtils.parseDateAutoCheck(projectValue));
                                } catch (Exception e) {
                                    throw new RuntimeException("创建日期无法识别");
                                }
                            } else if (titleValue.equals("项目备注")) {
                                quoteProject.setRemark(projectValue);
                            } else {
                                i = y - 1;
                                break;
                            }
                        }
                        continue;
                    }
                    if (cell0Value.equals("客户信息")) {
                        for (int y = i + 1; y < totalRows; y++) {
                            if(sheet.getRow(y) == null){
                                continue;
                            }
                            String customerValue = getCellValueString(sheet.getRow(y).getCell(1), evaluator);
                            String titleValue = getCellValueString(sheet.getRow(y).getCell(0), evaluator);
                            if (titleValue.equals("客户名称")) {
                                quoteCustomer.setCustFullName(customerValue);
                            } else if (titleValue.equals("联系人")) {
                                quoteCustomer.setContactsName(customerValue);
                            } else if (titleValue.equals("联系电话")) {
                                quoteCustomer.setContactsPhone(customerValue);
                            } else if (titleValue.equals("客户地址")) {
                                quoteCustomer.setCustAddress(customerValue);
                            } else if (titleValue.equals("客户邮编")) {
                                quoteCustomer.setPostCode(customerValue);
                            } else if (titleValue.equals("客户网址")) {
                                quoteCustomer.setCustWebSite(customerValue);
                            } else if (titleValue.equals("客户email")) {
                                quoteCustomer.setEmail(customerValue);
                            } else {
                                i = y - 1;
                                break;
                            }
                        }
                        continue;
                    }
                    if (cell0Value.contains("分类汇总") || cell0Value.equals("序号")) {
                        int num = cell0Value.contains("分类汇总") ? 2 : 1;
                        for (int y = i + num; y < totalRows; y++) {
                            if(sheet.getRow(y) == null){
                                continue;
                            }
                            String className = getCellValueString(sheet.getRow(y).getCell(1), evaluator);
                            if (oConvertUtils.isEmpty(className)) {
                                continue;
                            }
                            if (className.equals("小计")) {
                                quoteProject.setQty(getCellValueNumeric(sheet.getRow(y).getCell(2), evaluator));
                            } else if (className.equals("合计")) {
                                quoteProject.setAmount(getCellValueNumeric(sheet.getRow(y).getCell(3), evaluator));
                                // 项目sheet页解析结束
                                i = totalRows;
                                break;
                            } else if (className.equals("人民币大写")) {
                                // 项目sheet页解析结束
                                i = totalRows;
                                break;
                            } else if (className.contains("费")){
                                continue;
                            }else {
                                QuoteProductClass productClass = new QuoteProductClass();
                                productClass.setId(UUIDGenerator.generate());
                                productClass.setProjectId(quoteProject.getId());
                                productClass.setClassName(className);
                                productClass.setQty(getCellValueNumeric(sheet.getRow(y).getCell(2), evaluator));
                                productClass.setSumPrice(getCellValueNumeric(sheet.getRow(y).getCell(3), evaluator));
                                productClassList.add(productClass);
                                productClassNameAndId.put(className, productClass.getId());
                            }
                        }
                        continue;
                    }
                    if (cell0Value.contains("报表说明")) {
                        // 项目sheet页解析结束
                        break;
                    }
                }
                // 结果校验
                if (oConvertUtils.isEmpty(quoteProject.getProjectName())) {
                    throw new RuntimeException("未找到项目信息");
                }
                if (oConvertUtils.isEmpty(quoteCustomer.getCustFullName())) {
                    throw new RuntimeException("未找到客户信息");
                }
                if (oConvertUtils.listIsEmpty(productClassList)) {
                    throw new RuntimeException("未找到产品分类");
                }

                // 2.获取产品及产品明细
                List<QuoteProduct> productList = new ArrayList<>();
                List<QuoteProductDetail> productDetailList = new ArrayList<>();
                List<MdmMaterial> productMaterialList = new ArrayList<>();
                List<MdmMaterial> productDetailMaterialList = new ArrayList<>();
                List<QuoteProductFee> productFeeList = new ArrayList<>();
                int sheetCount = productClassList.size();
                for (int num = 1; num <= sheetCount; num++) {
                    // 按照产品sheet页顺序解析
                    sheet = workbook.getSheetAt(num);
                    if (sheet == null) {
                        break;
                    }
                    String sheetName = sheet.getSheetName();
                    if (!productClassNameAndId.containsKey(sheetName)) {
                        // 产品分类名称和sheet名称不匹配
                        continue;
                    }
                    // 2.1 获取产品、分类费用
                    totalRows = sheet.getPhysicalNumberOfRows();
                    List<QuoteProduct> sheetProductList = new ArrayList<>();
                    Map<String, String> productMaterialIdAndName = new HashMap<>();
                    int productEndRowNum = 0;
                    for (int j = sheet.getFirstRowNum(); j < totalRows; j++) {
                        if(sheet.getRow(j) == null){
                            continue;
                        }
                        String cell1Title = getCellValueString(sheet.getRow(j).getCell(1), evaluator);
                        String cell2Title = getCellValueString(sheet.getRow(j).getCell(2), evaluator);
                        String cell3Title = getCellValueString(sheet.getRow(j).getCell(3), evaluator);
                        if (cell1Title.equals("柜号") || cell2Title.equals("箱柜名称") || cell3Title.equals("箱柜型号")) {
                            int statStartRowNum = 0;
                            // 产品
                            for (int k = j + 1; k < totalRows; k++) {
                                if(sheet.getRow(k) == null){
                                    continue;
                                }
                                String cell0Value = getCellValueString(sheet.getRow(k).getCell(0), evaluator);
                                String cell1Value = getCellValueString(sheet.getRow(k).getCell(1), evaluator);
                                String cell2Value = getCellValueString(sheet.getRow(k).getCell(2), evaluator);
                                if (cell1Value.contains("合计") || cell0Value.contains("说明") || cell0Value.contains("报价人")) {
                                    // 无小计费用等信息
                                    productEndRowNum = k;
                                    j = totalRows;
                                    break;
                                } else if (cell1Value.contains("小计") || cell1Value.contains("包装费") || cell1Value.contains("运输费")
                                        || cell1Value.contains("运载工具车") || cell1Value.contains("合计")) {

                                    statStartRowNum = k;
                                    productEndRowNum = k;
                                    break;
                                } else if (oConvertUtils.isEmpty(cell2Value)) {
                                    productEndRowNum = k;
                                    continue;
                                } else {
                                    String cell3Value = getCellValueString(sheet.getRow(k).getCell(3), evaluator);
                                    String productMaterialName = cell1Value + cell2Value;
                                    MdmMaterial productMaterial = productMaterialList.stream().filter(material -> {
                                        return productMaterialName.equals(material.getMaterialName())
                                                && cell3Value.equals(material.getMaterialSpec());
                                    }).findFirst().orElse(null);
                                    if (productMaterial == null) {
                                        productMaterial = new MdmMaterial();
                                        productMaterial.setId(UUIDGenerator.generate());
                                        productMaterial.setMaterialCode(cell1Value);
                                        productMaterial.setMaterialName(productMaterialName);
                                        productMaterial.setMaterialSpec(cell3Value);
                                        productMaterial.setMaterialUnitCode(getCellValueString(sheet.getRow(k).getCell(4), evaluator));
                                        productMaterialList.add(productMaterial);
                                    }
                                    productMaterialIdAndName.put(productMaterial.getId(), cell2Value);
                                    //产品
                                    QuoteProduct product = new QuoteProduct();
                                    product.setId(UUIDGenerator.generate());
                                    product.setProjectId(quoteProject.getId());
                                    product.setProductClassId(productClassNameAndId.get(sheetName));
                                    product.setMaterialId(productMaterial.getId());
                                    product.setMaterialDesc(productMaterial.getMaterialName() + productMaterial.getMaterialSpec());
                                    product.setQty(getCellValueNumeric(sheet.getRow(k).getCell(5), evaluator));
                                    product.setUnitPrice(getCellValueNumeric(sheet.getRow(k).getCell(6), evaluator));
                                    product.setSumPrice(getCellValueNumeric(sheet.getRow(k).getCell(7), evaluator));
                                    product.setRemark(getCellValueString(sheet.getRow(k).getCell(8), evaluator));
                                    sheetProductList.add(product);

                                    productEndRowNum = k;
                                }
                            }
                            // 分类费用
                            if (statStartRowNum > 0) {
                                QuoteProductClass curProductClass = productClassList.stream().filter(productClass -> productClass.getId().equals(productClassNameAndId.get(sheetName))).findFirst().get();
                                for (int x = statStartRowNum; x < totalRows; x++) {
                                    if(sheet.getRow(x) == null){
                                        continue;
                                    }
                                    String cell1Value = getCellValueString(sheet.getRow(x).getCell(1), evaluator);
                                    BigDecimal cell7Value = getCellValueNumeric(sheet.getRow(x).getCell(7), evaluator);
                                    if (cell1Value.contains("小计")) {
                                        curProductClass.setProductPrice(cell7Value);
                                    } else if (cell1Value.contains("包装费")) {
                                        curProductClass.setPackingPrice(cell7Value);
                                    } else if (cell1Value.contains("运输费")) {
                                        curProductClass.setTransportPrice(cell7Value);
                                    } else if (cell1Value.contains("运载工具车")) {
                                        curProductClass.setTransportCar(cell7Value);
                                    } else {
                                        productEndRowNum = x;
                                        j = totalRows;
                                        break;
                                    }
                                }
                            }
                        }
                    }
                    if (oConvertUtils.listIsEmpty(sheetProductList)) {
                        // 未找到产品信息，继续查找下一个sheet页
                        continue;
                    } else {
                        productList.addAll(sheetProductList);
                    }
                    // 2.2 获取产品明细
                    List<QuoteProductDetail> sheetProductDetailList = new ArrayList<>();
                    if (productEndRowNum == 0) {
                        continue;
                    }
                    for (int j = productEndRowNum; j < totalRows; j++) {
                        if(sheet.getRow(j) == null){
                            continue;
                        }
                        if (getCellValueString(sheet.getRow(j).getCell(5), evaluator).contains("名称")
                                && getCellValueString(sheet.getRow(j).getCell(0), evaluator).contains("柜号")
                                && getCellValueString(sheet.getRow(j).getCell(2), evaluator).contains("型号")) {
                            // 查找明细对应产品
                            String product1Value = getCellValueString(sheet.getRow(j).getCell(1), evaluator);
                            String product3Value = getCellValueString(sheet.getRow(j).getCell(3), evaluator);
                            String product6Value = getCellValueString(sheet.getRow(j).getCell(6), evaluator);
                            MdmMaterial findProductMaterial = productMaterialList.stream()
                                    .filter(material -> {
                                        return product1Value.equals(material.getMaterialCode())
                                                && product3Value.equals(material.getMaterialSpec())
                                                && product6Value.equals(productMaterialIdAndName.get(material.getId()));
                                    }).findFirst().orElse(null);
                            if (findProductMaterial == null) {
                                continue;
                            }
                            QuoteProduct findProduct = sheetProductList.stream()
                                    .filter(product -> product.getMaterialId().equals(findProductMaterial.getId())).findFirst().orElse(null);
                            if (findProduct == null) {
                                continue;
                            }
                            // 解析产品明细
                            String costUnitPriceTitle = getCellValueString(sheet.getRow(j+1).getCell(10), evaluator);
                            String costSumPriceTitle = getCellValueString(sheet.getRow(j+1).getCell(11), evaluator);
                            for (int k = j + 2; k < totalRows; k++) {
                                if(sheet.getRow(k) == null){
                                    continue;
                                }
                                String cell0Value = getCellValueString(sheet.getRow(k).getCell(0), evaluator);
                                String cell1Value = getCellValueString(sheet.getRow(k).getCell(1), evaluator);
                                if (cell0Value.contains("总计") || cell0Value.contains("报价人")) {
                                    j = k;
                                    break;
                                } else if (oConvertUtils.isEmpty(cell1Value) || cell1Value.contains("小计") || cell1Value.contains("合计")) {
                                    continue;
                                } else if (cell1Value.contains("费")) {
                                    QuoteProductFee quoteProductFee = new QuoteProductFee();
                                    quoteProductFee.setId(UUIDGenerator.generate());
                                    quoteProductFee.setMainId(findProduct.getId());
                                    quoteProductFee.setFeeId(cell1Value);
                                    quoteProductFee.setFeePrice(getCellValueNumeric(sheet.getRow(k).getCell(7), evaluator));
                                    quoteProductFee.setCostFeePrice(getCellValueNumeric(sheet.getRow(k).getCell(11), evaluator));
                                    productFeeList.add(quoteProductFee);
                                } else if (cell1Value.contains("税金")) {
                                    findProduct.setTaxPrice(getCellValueNumeric(sheet.getRow(k).getCell(7), evaluator));
                                } else {
                                    BigDecimal cell5Value = getCellValueNumeric(sheet.getRow(k).getCell(5), evaluator);
                                    if (cell5Value.compareTo(BigDecimal.ZERO) == 0) {
                                        // 数量为0，无效产品明细
                                        continue;
                                    }
                                    String cell2Value = getCellValueString(sheet.getRow(k).getCell(2), evaluator);
                                    String cell3Value = getCellValueString(sheet.getRow(k).getCell(3), evaluator);
                                    MdmMaterial findProductDetailMaterial = productDetailMaterialList.stream().filter(materialDetail -> {
                                        return materialDetail.getMaterialName().equals(cell1Value)
                                                && materialDetail.getMaterialSpec().equals(cell2Value)
                                                && materialDetail.getMaterialFactory().equals(cell3Value);
                                    }).findFirst().orElse(null);
                                    if (findProductDetailMaterial == null) {
                                        // 添加产品明细物料
                                        findProductDetailMaterial = new MdmMaterial();
                                        findProductDetailMaterial.setId(UUIDGenerator.generate());
                                        findProductDetailMaterial.setMaterialName(cell1Value);
                                        findProductDetailMaterial.setMaterialSpec(cell2Value);
                                        findProductDetailMaterial.setMaterialFactory(cell3Value);
                                        findProductDetailMaterial.setMaterialUnitCode(getCellValueString(sheet.getRow(k).getCell(4), evaluator));
                                        productDetailMaterialList.add(findProductDetailMaterial);
                                    }
                                    // 添加产品明细
                                    QuoteProductDetail quoteProductDetail = new QuoteProductDetail();
                                    quoteProductDetail.setId(UUIDGenerator.generate());
                                    quoteProductDetail.setMainId(findProduct.getId());
                                    quoteProductDetail.setMaterialId(findProductDetailMaterial.getId());
                                    quoteProductDetail.setMaterialFactory(findProductDetailMaterial.getMaterialFactory());
                                    quoteProductDetail.setMaterialDesc(findProductDetailMaterial.getMaterialName() + findProductDetailMaterial.getMaterialSpec());
                                    quoteProductDetail.setUnit(findProductDetailMaterial.getMaterialUnitCode());
                                    quoteProductDetail.setSubQty(cell5Value);
                                    quoteProductDetail.setQty(cell5Value.multiply(findProduct.getQty()).setScale(2,RoundingMode.HALF_UP));
                                    quoteProductDetail.setUnitPrice(getCellValueNumeric(sheet.getRow(k).getCell(6), evaluator));
                                    quoteProductDetail.setSumPrice(quoteProductDetail.getUnitPrice().multiply(quoteProductDetail.getQty()).setScale(2, RoundingMode.HALF_UP));
                                    quoteProductDetail.setSubSumPrice(getCellValueNumeric(sheet.getRow(k).getCell(7), evaluator));
                                    quoteProductDetail.setRemark(getCellValueString(sheet.getRow(k).getCell(8), evaluator));
                                    if(costUnitPriceTitle.equals("成本单价")){
                                        quoteProductDetail.setCostUnitPrice(getCellValueNumeric(sheet.getRow(k).getCell(10), evaluator));
                                    }else{
                                        quoteProductDetail.setCostUnitPrice(BigDecimal.ZERO);
                                    }
                                    if(costSumPriceTitle.equals("成本合计")){
                                        quoteProductDetail.setSubCostSumPrice(getCellValueNumeric(sheet.getRow(k).getCell(11), evaluator));
                                    }else{
                                        quoteProductDetail.setSubCostSumPrice(BigDecimal.ZERO);
                                    }
                                    quoteProductDetail.setCostSumPrice(quoteProductDetail.getCostUnitPrice().multiply(quoteProductDetail.getQty()).setScale(2, RoundingMode.HALF_UP));
                                    sheetProductDetailList.add(quoteProductDetail);
                                }
                            }
                        } else {
                            continue;
                        }
                    }
                    productDetailList.addAll(sheetProductDetailList);
                }

                // 3.保存数据
                quoteProductService.saveQuoteProductImport(quoteProject, quoteCustomer, productClassList, productList,
                        productDetailList, productMaterialList, productDetailMaterialList, productFeeList);

                return Result.OK("文件导入成功！");
            } catch (Exception e) {
                log.error(e.getMessage(), e);
                return Result.error("文件导入失败:" + e.getMessage());
            } finally {
                try {
                    file.getInputStream().close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
        return Result.OK("文件导入失败！");
    }

    private BigDecimal getCellValueNumeric(Cell cell, XSSFFormulaEvaluator evaluator) {
        BigDecimal value = BigDecimal.ZERO;
        if (cell == null) {
            return value;
        }
        CellType cellType = cell.getCellType();
        try {
            if (cell.getCellType().equals(CellType.NUMERIC)) {
                value = new BigDecimal(cell.getNumericCellValue());
            } else if(cell.getCellType().equals(CellType.FORMULA)){
                value = new BigDecimal(evaluator.evaluate(cell).getNumberValue());
            } else {
                value = new BigDecimal(cell.getStringCellValue());
            }
        } catch (Exception e) {
            log.error("excel单元格数据获取失败：{}", e.getMessage());
        }

        return value == null ? BigDecimal.ZERO : value.setScale(2, RoundingMode.HALF_UP);
    }

    private String getCellValueString(Cell cell, XSSFFormulaEvaluator evaluator) {
        if (cell == null) {
            return "";
        }
        String value = null;
        try {
            if (cell.getCellType().equals(CellType.NUMERIC)) {
                value = new BigDecimal(cell.getNumericCellValue()).setScale(2,RoundingMode.HALF_UP).toPlainString();
            } else if(cell.getCellType().equals(CellType.FORMULA)){
                value = evaluator.evaluate(cell).getStringValue();
            }else {
                value = cell.getStringCellValue();
            }
        } catch (Exception e) {
            log.error("excel单元格数据获取失败：{}", e.getMessage());
        }

        return value == null ? "" : oConvertUtils.replaceBlank(value);
    }

}
